xxxxxxxxxx

xxxxxxxxxx # Import the required libraries and dependenciesimport osimport numpyimport requestsimport jsonimport pandas as pdfrom dotenv import load_dotenvimport alpaca_trade_api as tradeapifrom MCForecastTools import MCSimulationimport sysimport pandas as pdfrom pathlib import Path​​%matplotlib inline

x stock = (input("Enter the stock you own: "))shares = float(input("Enter the number of shares: "))quarterly_dividend = float(input("Enter the quarterly dividend amount: "))yearly_rate = float(input("Enter the yearly rate of return: "))​​total_quarterly_dividend = quarterly_dividend * sharesquarterly_rate = yearly_rate/4/100years = int(input("Enter the number of years you want to simulate: "))quarters = years * 4futureValue = total_quarterly_dividend * ((((1 + quarterly_rate)**(quarters))-1) * (1 + quarterly_rate))/quarterly_ratefutureValue = round(futureValue)print("The expected amount of divdends from {stock} you will receive is:",futureValue)

Enter the stock you own:  msft
Enter the number of shares:  300
Enter the quarterly dividend amount:  .62
Enter the yearly rate of return:  1
Enter the number of years you want to simulate:  20

The expected amount of divdends from {stock} you will receive is: 16491

​

​

 # Load the environment variables from the .env file# by calling the load_dotenv functionload_dotenv()

xxxxxxxxxx

xxxxxxxxxx

 # Current amount of shares held in MSFT.msft_shares = 300​

xxxxxxxxxx

xxxxxxxxxx

 # Set the variables for the Alpaca API and secret keysalpaca_api_key = os.getenv("ALPACA_API_KEY")alpaca_secret_key = os.getenv("ALPACA_SECRET_KEY")

 # Create the Alpaca tradeapi.REST objectalpaca = tradeapi.REST(    alpaca_api_key,    alpaca_secret_key,    api_version="v2")

xxxxxxxxxx

xxxxxxxxxx # Set the tickers for both the bond and stock portion of the portfoliotickers = ['MSFT']# 'T', 'MO', 'JPM', 'F' ()​# Set timeframe to 1D timeframe = "1D"​# Format current date as ISO format# Set both the start and end date at the date of your prior weekday # This will give you the closing price of the previous trading day# Alternatively you can use a start and end date of 2020-08-07start_date = pd.Timestamp("2021-10-29", tz="America/New_York").isoformat()end_date = pd.Timestamp("2021-10-29", tz="America/New_York").isoformat()

xxxxxxxxxx

 # Use the Alpaca get_barset function to get current closing prices the portfolio# Be sure to set the `df` property after the function to format the response object as a DataFramemsft_portfolio = alpaca.get_barset(    tickers,    timeframe,    start = start_date,    end = end_date).df​# Review the first 5 rows of the Alpaca DataFramemsft_portfolio.head()

xxxxxxxxxx

 # Access the closing price for MSFT from the Alpaca DataFrame# Converting the value to a floating point numbermsft_close_price = float(msft_portfolio['MSFT']['close'])# Print the MSFT closing priceprint(f'The MSFT closing price is: ${msft_close_price:.2f}')

 # Access the total value of the Microsoft holding by multiplying the closing price by the number of shares already determines.msft_value=msft_close_price*msft_shares

 # Set start and end dates of 5 years back from your current date# Alternatively, you can use an end date of 2020-08-07 and work 5 years back from that date start_date = "2016-10-29" end_date = "2021-10-29"​# Set number of rows to 1000 to retrieve the maximum amount of rowslimit_rows=1000

 # Use the Alpaca get_barset function to make the API call to get the 3 years worth of pricing data# The tickers and timeframe parameters should have been set in Part 1 of this activity # The start and end dates should be updated with the information set above# Remember to add the df property to the end of the call so the response is returned as a DataFramefive_yr_portfolio = alpaca.get_barset(    tickers,    timeframe,    start = start_date,     end = end_date,    limit = limit_rows).df​# Display both the first and last five rows of the DataFramedisplay(five_yr_portfolio.head())display(five_yr_portfolio.tail())

xxxxxxxxxx

xxxxxxxxxx

xxxxxxxxxx

xxxxxxxxxx

 # Print the current balance of the stock and bond portion of the members portfolioprint(f'The current balance of the MSFT portfolio is: ${msft_value:.2f}')

 # Configure a Monte Carlo simulation to forecast 10 years cumulative returns# Run 500 samples.ten_year_simulation = MCSimulation(    portfolio_data=five_yr_portfolio,    #weights=[0.20, 0.80],    num_simulation=1000,    num_trading_days=252*10)# Review the simulation input dataten_year_simulation.portfolio_data.head()

 # Run the Monte Carlo simulation to forecast 10 years cumulative returnsten_year_simulation.calc_cumulative_return()

 # Visualize the 10-year Monte Carlo simulation by creating an# overlay line plotten_year_simulation.plot_simulation()

 # Visualize the probability distribution of the 10-year Monte Carlo simulation # by plotting a histogramten_year_simulation.plot_distribution()​

 # Generate summary statistics from the 10-year Monte Carlo simulation results# Save the results as a variableten_tbl=ten_year_simulation.summarize_cumulative_return()​# Review the 10-year Monte Carlo summary statisticsten_tbl

 # Use the lower and upper `95%` confidence intervals to calculate the range of the possible outcomes for the current stock/bond portfolioci_lower_ten_cumulative_return = round(ten_tbl[8] * msft_value,2)ci_upper_ten_cumulative_return = round(ten_tbl[9] * msft_value,2)​# Print the result of your calculationsprint(f"There is a 95% chance that your current stock/bond portfolio"      f" over the next ten years will end within in the range of"      f" ${ci_lower_ten_cumulative_return:.2f} and ${ci_upper_ten_cumulative_return:.2f}.")​

 # Print the current balance of the stock and bond portion of the members portfolioprint(f'The current balance of the MSFT portfolio is: ${msft_value:.2f}')

 # Configure a Monte Carlo simulation to forecast 20 years cumulative returns# Run 1000 samples.twenty_year_simulation = MCSimulation(    portfolio_data=five_yr_portfolio,    #weights=[0.20, 0.80],    num_simulation=1000,    num_trading_days=252*20,)# Review the simulation input datatwenty_year_simulation.portfolio_data.head()

 # Run the Monte Carlo simulation to forecast 10 years cumulative returnstwenty_year_simulation.calc_cumulative_return()

 # Visualize the 10-year Monte Carlo simulation by creating an# overlay line plottwenty_year_simulation.plot_simulation()

 # Visualize the probability distribution of the 10-year Monte Carlo simulation # by plotting a histogramtwenty_year_simulation.plot_distribution()​

 # Generate summary statistics from the 20-year Monte Carlo simulation results# Save the results as a variabletwenty_tbl=twenty_year_simulation.summarize_cumulative_return()​# Review the 10-year Monte Carlo summary statisticstwenty_tbl

 # Use the lower and upper `95%` confidence intervals to calculate the range of the possible outcomes for the current stock/bond portfolioci_lower_twenty_cumulative_return = round(twenty_tbl[8] * msft_value,2)ci_upper_twenty_cumulative_return = round(twenty_tbl[9] * msft_value,2)​# Print the result of your calculationsprint(f"There is a 95% chance that your current stock/bond portfolio"      f" over the next year will end within in the range of"      f" ${ci_lower_twenty_cumulative_return} and ${ci_upper_twenty_cumulative_return}.")

 # Print the current balance of the MSFT portfolioprint(f'The current balance of the MSFT portfolio is: ${msft_value:.2f}')

 # Configure the Monte Carlo simulation to forecast 30 years cumulative returns# Run 1000 samples.thirty_year_simulation = MCSimulation(    portfolio_data = five_yr_portfolio,    num_simulation = 1000,    num_trading_days = 252 * 30,)# Review the simulation input datathirty_year_simulation.portfolio_data.head()# Set number of simulationsnum_sims = 1000

 # Run the Monte Carlo simulation to forecast 30 years cumulative returnsthirty_year_simulation.calc_cumulative_return()

 ### Visualize the 30-year Monte Carlo simulation by creating an# overlay line plotthirty_year_simulation.plot_simulation()

 # Visualize the probability distribution of the 30-year Monte Carlo simulation # by plotting a histogramthirty_year_simulation.plot_distribution()

 # Generate summary statistics from the 30-year Monte Carlo simulation results# Save the results as a variablethirty_tbl=thirty_year_simulation.summarize_cumulative_return()​# Review the 30-year Monte Carlo summary statisticsthirty_tbl

 # Use the lower and upper `95%` confidence intervals to calculate the range of the possible outcomes for the current stock/bond portfolioci_lower_thirty_cumulative_return = round(thirty_tbl[8] * msft_value,2)ci_upper_thirty_cumulative_return = round(thirty_tbl[9] * msft_value,2)​# Print the result of your calculationsprint(f"There is a 95% chance that your current stock/bond portfolio"      f" over the next year will end within in the range of"      f" ${ci_lower_thirty_cumulative_return:.2f} and ${ci_upper_thirty_cumulative_return:.2f}.")

xxxxxxxxxx

 # Import the required libraries and dependenciesimport osfrom pathlib import Pathimport requestsimport jsonimport pandas as pdfrom dotenv import load_dotenvimport alpaca_trade_api as tradeapiimport hvplot.pandasfrom MCForecastTools import MCSimulation​%matplotlib inline

x# Load the environment variables from the .env file# by calling the load_dotenv functionload_dotenv()

True

xxxxxxxxxx

xxxxxxxxxx

 # Current amount of shares held in the stock (MSFT) portfolio.msft_shares = 300

xxxxxxxxxx

xxxxxxxxxx

x# Set Alpaca API key and secretalpaca_api_key = os.getenv("ALPACA_API_KEY")alpaca_secret_key = os.getenv("ALPACA_SECRET_KEY")

x# Create the Alpaca API objectalpaca = tradeapi.REST(    alpaca_api_key,    alpaca_secret_key,    api_version="v2")

xxxxxxxxxx

x# Set the tickerstickers = ["MSFT"]

 # Set timeframe to one day ('1D') for the Alpaca APItimeframe = "1D"

 # Format current date as ISO formatstart_date = pd.Timestamp("2021-10-29", tz="America/New_York").isoformat()end_date = pd.Timestamp("2021-10-29", tz="America/New_York").isoformat()

xxxxxxxxxx

 # Use the Alpaca get_barset function to get current closing prices the portfoliocurrent_msft_price_df = alpaca.get_barset(    tickers,    timeframe,    start = start_date,    end = end_date).df​# Review the first 5 rows of the Alpaca DataFramecurrent_msft_price_df.head()

xxxxxxxxxx

 # Access the closing price for MSFT from the Alpaca DataFrame# Converting the value to a floating point numbermsft_close_price = float(current_msft_price_df["MSFT"]["close"])​# Print the MSFT closing priceprint(msft_close_price)

331.64

xxxxxxxxxx

 # Calculate the current value of the portfoliomsft_value = round(msft_shares * msft_close_price, 3)​# Print the current value of the bond portfolioprint(f"The portofolio has a value of: {msft_value}")​

The portofolio has a value of: 99492.0

xxxxxxxxxx

x# Import the data by reading in the CSV file# Review the first 5 rows of the DataFramemsft_div = pd.read_csv(Path("Resources/msft_dividends.csv"))​msft_div.head()

xxxxxxxxxx

 # Use the str.replace function to remove the dollar sign, $msft_div.loc[:, "Amount"]= msft_div.loc[:, "Amount"].str.replace("$", "")

/opt/anaconda3/envs/dev/lib/python3.7/site-packages/ipykernel_launcher.py:2: FutureWarning: The default value of regex will change from True to False in a future version. In addition, single character regular expressions will*not* be treated as literal strings when regex=True.

xxxxxxxxxx

 # Convert the data type to a floatmsft_div.loc[:, "Amount"]= msft_div.loc[:, "Amount"].astype("float")​msft_div.dtypes

Announced        object
Period           object
Amount          float64
Yield            object
Ex-Dividend      object
Record Date      object
Payable Date     object
dtype: object

xxxxxxxxxx

 # Use loc to select `Timestamp (the index)` and `Close` from bitstamp DataFramemsft_div_slices = msft_div.loc[:, "Amount"]​# Review the first five rows of the DataFramemsft_div_slices.head()

0    0.62
1    0.56
2    0.56
3    0.56
4    0.56
Name: Amount, dtype: float64

xxxxxxxxxx

 # Calculating the average quaterly dividend for MSFTmsft_div_slices_average = round(msft_div_slices.mean(),2)msft_div_slices_average

0.46

 # calculating the Yearly average dividend for MSFT shareaverage_year_div_msft = msft_div_slices_average * 4print(f"The average yearly dividend for Microsoft is: ${average_year_div_msft} per share.")

The average yearly dividend for Microsoft is: $1.84 per share.

xxxxxxxxxx

xxxxxxxxxx

 # The Free Crypto API Call endpoint URLs for the held cryptocurrency assetsbtc_url = "https://api.alternative.me/v2/ticker/Bitcoin/?convert=USD"

xxxxxxxxxx

 # Using the Python requests library, make an API call to access the current price of BTCbtc_response = requests.get(btc_url).json()​# Use the json.dumps function to review the response data from the API call# Use the indent and sort_keys parameters to make the response object readableprint(json.dumps(btc_response, indent=4, sort_keys=True))

{
    "data": {
        "1": {
            "circulating_supply": 18860950,
            "id": 1,
            "last_updated": 1635794052,
            "max_supply": 21000000,
            "name": "Bitcoin",
            "quotes": {
                "USD": {
                    "market_cap": 1148190624646,
                    "percent_change_1h": 0.273737200904498,
                    "percent_change_24h": 0.213312546616552,
                    "percent_change_7d": -0.318576816212922,
                    "percentage_change_1h": 0.273737200904498,
                    "percentage_change_24h": 0.213312546616552,
                    "percentage_change_7d": -0.318576816212922,
                    "price": 60978.0,
                    "volume_24h": 35977662583
                }
            },
            "rank": 1,
            "symbol": "BTC",
            "total_supply": 18860950,
            "website_slug": "bitcoin"
        }
    },
    "metadata": {
        "error": null,
        "num_cryptocurrencies": 3105,
        "timestamp": 1635794052
    }
}

xxxxxxxxxx

 # Navigate the BTC response object to access the current price of BTCbtc_price = btc_response['data']['1']['quotes']['USD']['price']​# Print the current price of BTCprint(f'The current price of BTC is ${btc_price:.2f}')

The current price of BTC is $60978.00

xxxxxxxxxx

xxxxxxxxxx

 # impoprt New version of the Montecarlofrom MCForecastTools_2Mod import MCSimulation

xxxxxxxxxx

 # Set start and end dates of 3 years back from your current date# Alternatively, you can use an end date of 2020-08-07 and work 3 years back from that date start_date = pd.Timestamp("2016-10-29", tz="America/New_York").isoformat() end_date = pd.Timestamp("2021-10-29", tz="America/New_York").isoformat()​# Set number of rows to 1000 to retrieve the maximum amount of rowslimit_rows = 1000

 # Use the Alpaca get_barset function to make the API call to get the 3 years worth of pricing data# The tickers and timeframe parameters should have been set early of this activity # The start and end dates should be updated with the information set above# Adding the df property to the end of the call so the response is returned as a DataFrameprices_df = alpaca.get_barset(tickers, timeframe, start= start_date, end= end_date, limit= limit_rows).df​​# Display both the first and last five rows of the DataFramedisplay(prices_df.head())display(prices_df.tail())

xxxxxxxxxx

 # Configure the Monte Carlo simulation to forecast 10 years cumulative returns# Run 1000 samples.MC_ten_years = MCSimulation(portfolio_data = prices_df,                             num_simulation= 1000,                             num_trading_days= 252*10)​# Review the simulation input dataMC_ten_years.portfolio_data.head()​​# We need to update the Montecarlo first for this part!!!

 # Run the Monte Carlo simulation to forecast 10 years cumulative returnsMC_ten_years.calc_cumulative_return()​

 # Visualize the 10-year Monte Carlo simulation by creating an# overlay line plotMC_ten_years.plot_simulation()​

xxxxxxxxxx

 # Visualize the probability distribution of the 10-year Monte Carlo simulation # by plotting a histogramMC_thirty_years.plot_distribution()

xxxxxxxxxx

 # Generate summary statistics from the 10-year Monte Carlo simulation results# Save the results as a variableMC_ten_summary_statistics = round(MC_ten_years.summarize_cumulative_return(), 3)​​# Review the 10-year Monte Carlo summary statisticsprint(MC_ten_summary_statistics)

xxxxxxxxxx

 # Print the current value of the stock.print(f"The current value of the stock portfolio is: ${msft_value}")​

 # Use the lower and upper `95%` confidence intervals to calculate the range of the possible outcomes for the portfolioci_lower_ten_cumulative_return = MC_ten_summary_statistics[8] * msft_valueci_upper_ten_cumulative_return = MC_ten_summary_statistics[9] * msft_value​# Print the result of your calculationsprint(f"There is a 95% chance that you current protfolio over the next 30 years will end within in range of \n"     f"${ci_lower_ten_cumulative_return: .2f} and ${ci_upper_ten_cumulative_return: .2f}")​

​

xxxxxxxxxx

 # Configure the Monte Carlo simulation to forecast 20 years cumulative returns# Run 1000 samples.MC_twenty_years = MCSimulation(portfolio_data = prices_df,                             num_simulation= 1000,                             num_trading_days= 252*20)​# Review the simulation input dataMC_twenty_years.portfolio_data.head()​​# We need to update the Montecarlo first for this part!!!

 # Run the Monte Carlo simulation to forecast 20 years cumulative returnsMC_twenty_years.calc_cumulative_return()​

 # Visualize the 20-year Monte Carlo simulation by creating an# overlay line plotMC_twenty_years.plot_simulation()​

xxxxxxxxxx

 # Visualize the probability distribution of the 20-year Monte Carlo simulation # by plotting a histogramMC_thirty_years.plot_distribution()

xxxxxxxxxx

 # Generate summary statistics from the 20-year Monte Carlo simulation results# Save the results as a variableMC_twenty_summary_statistics = round(MC_twenty_years.summarize_cumulative_return(), 3)​​# Review the 20-year Monte Carlo summary statisticsprint(MC_twenty_summary_statistics)

xxxxxxxxxx

 # Print the current value of the stock.print(f"The current value of the stock portfolio is: ${msft_value}")​

 # Use the lower and upper `95%` confidence intervals to calculate the range of the possible outcomes for the portfolioci_lower_twenty_cumulative_return = MC_twenty_summary_statistics[8] * msft_valueci_upper_twenty_cumulative_return = MC_twenty_summary_statistics[9] * msft_value​# Print the result of your calculationsprint(f"There is a 95% chance that you current protfolio over the next 30 years will end within in range of \n"     f"${ci_lower_twenty_cumulative_return: .2f} and ${ci_upper_twenty_cumulative_return: .2f}")​

​

xxxxxxxxxx

 # Configure the Monte Carlo simulation to forecast 30 years cumulative returns# Run 1000 samples.MC_thirty_years = MCSimulation(portfolio_data = prices_df,                             num_simulation= 1000,                             num_trading_days= 252*30)​# Review the simulation input dataMC_thirty_years.portfolio_data.head()​​# We need to update the Montecarlo first for this part!!!

 # Run the Monte Carlo simulation to forecast 30 years cumulative returnsMC_thirty_years.calc_cumulative_return()​

 # Visualize the 30-year Monte Carlo simulation by creating an# overlay line plotMC_thirty_years.plot_simulation()​

xxxxxxxxxx

 # Visualize the probability distribution of the 30-year Monte Carlo simulation # by plotting a histogramMC_thirty_years.plot_distribution()

xxxxxxxxxx

 # Generate summary statistics from the 30-year Monte Carlo simulation results# Save the results as a variableMC_thirty_summary_statistics = round(MC_thirty_years.summarize_cumulative_return(), 3)​​# Review the 30-year Monte Carlo summary statisticsprint(MC_thirty_summary_statistics)

xxxxxxxxxx

 # Print the current value of the stock.print(f"The current value of the stock portfolio is: ${msft_value}")​

 # Use the lower and upper `95%` confidence intervals to calculate the range of the possible outcomes for the portfolioci_lower_thirty_cumulative_return = MC_thirty_summary_statistics[8] * msft_valueci_upper_thirty_cumulative_return = MC_thirty_summary_statistics[9] * msft_value​# Print the result of your calculationsprint(f"There is a 95% chance that you current protfolio over the next 30 years will end within in range of \n"     f"${ci_lower_thirty_cumulative_return: .2f} and ${ci_upper_thirty_cumulative_return: .2f}")​

​

xxxxxxxxxx

xxxxxxxxxx

​

​

​

​

xxxxxxxxxx

xxxxxxxxxx

​

​

​

 ​class stock():    def __init__(self,name,wkn):        self.name = name        self.wkn = wkn​    def set_div_values(self,div_yield,div_growth,share_growth_pa,price):        self.price = price        self.div_yield = div_yield / 100        self.div_growth = div_growth / 100        self.share_growth_pa = share_growth_pa / 100        self.div = price * div_yield / 100        self.div_sum = 0​    # calc compounded dividends p.a grown over time    def calc_div(self,time, reinvest = True):​        dividends = dict()        dividends[0] = [self.div]​        if reinvest is True:            for t in range(1,time+1):                temp = []                for i in range(t):                    temp.append(dividends[t-1][i]*(1+self.div_growth))​                div_sum_reinvest = sum(dividends[t - 1])                temp.append(div_sum_reinvest*self.div_yield)                dividends[t] = temp​            n = len(dividends.keys())-1            div_pa = sum(dividends[n])​        else:            div_pa = self.div            for t in range(1,time+1):                div_pa = div_pa * (1+self.div_growth)                #print(f"{t}:  Div: {div} ")​        return div_pa​    # calc compount interest    def calc_endcap(self,time):        return self.price * pow(1+self.share_growth_pa,time)​​coke = stock("Coca Cola","850663")coke.set_div_values(3.1,5,5,100)​coke_dividends_re = coke.calc_div(20)coke_dividends = coke.calc_div(20,False)coke_endcap = coke.calc_endcap(20)print(f"dividends reinveste:\t{coke_dividends_re}\ndividends not reinveste:\t{coke_dividends}\nendcap: \t{coke_endcap}")

xxxxxxxxxx

xxxxxxxxxx

 # Importing the required libraries and dependenciesimport numpy as npimport pandas as pdimport hvplot.pandasimport sqlalchemyfrom pathlib import Pathfrom datetime import datetime​​# Create a temporary SQLite database and populate the database with content from the etf.db seed filedatabase_connection_string = 'sqlite:///etf.db'​# Create an engine to interact with the SQLite databaseengine = sqlalchemy.create_engine(database_connection_string)​# Confirm that table names contained in the SQLite database.engine.table_names()

C:\Users\jakob\miniforge3\envs\dev\lib\site-packages\ipykernel_launcher.py:17: SADeprecationWarning: The Engine.table_names() method is deprecated and will be removed in a future release.  Please refer to Inspector.get_table_names(). (deprecated since: 1.4)

['GDOT', 'GS', 'PYPL', 'SQ']

xxxxxxxxxx

xxxxxxxxxx

 # Write a SQL query to SELECT all of the data from the PYPL tablequery = """SELECT * FROM PYPL;"""​# Use the query to read the PYPL data into a Pandas DataFramepypl_dataframe = pd.read_sql_query(query, con=engine)

 # Reflect only date on time column  pypl_dataframe["time"] = pd.to_datetime(pypl_dataframe["time"], infer_datetime_format= True)​# changing index column to timepypl_dataframe.set_index("time", drop= True, inplace= True)

xxxxxxxxxx

 # View the first 5 rows of the DataFrame.pypl_dataframe.head()

 # View the last 5 rows of the DataFrame.pypl_dataframe.tail()

xxxxxxxxxx

 # Create an interactive visualization with hvplot to plot the daily returns for PYPL.pypl_dataframe.hvplot(    x= 'time',    y= ['daily_returns'],     title=('PYPL Daily Returns'),    height=400,    width=800)

xxxxxxxxxx

 # Create an interactive visaulization with hvplot to plot the cumulative returns for PYPL.pypl_cumulative_returns = (1+ pypl_dataframe['daily_returns']).cumprod()​pypl_cumulative_returns.hvplot(    x='time',    title=('PYPL Cumulative Returns'),     height=400,    width=800)

xxxxxxxxxx

xxxxxxxxxx

 # Write a SQL SELECT statement to select the time and close columns # where the PYPL closing price was higher than 200.0.query = """SELECT time, close FROM PYPLWHERE close > 200"""​# Using the query, read the data from the database into a Pandas DataFramepypl_higher_than_200 = pd.read_sql_query(query, con=engine)​# Review the resulting DataFramepypl_higher_than_200

xxxxxxxxxx

 # Write a SQL SELECT statement to select the time and daily_returns columns# Sort the results in descending order and return only the top 10 return valuesquery= """SELECT time, daily_returnsFROM PYPLORDER BY daily_returns DESCLIMIT 10"""​# Using the query, read the data from the database into a Pandas DataFramepypl_top_10_returns = pd.read_sql_query(query, con=engine)​# Review the resulting DataFramepypl_top_10_returns

xxxxxxxxxx

xxxxxxxxxx

 # Write a SQL query to join each table in the portfolio into a single DataFrame # Use the time column from each table as the basis for the joinquery = """SELECT *FROM GDOTJOIN GS ON GS.time = GDOT.timeJOIN PYPL ON PYPL.time = GDOT.timeJOIN SQ ON SQ.time = GDOT.time"""​# Using the query, read the data from the database into a Pandas DataFrameetf_portfolio = pd.read_sql_query(query, con=engine)​# Review the resulting DataFramecloseetf_portfolio.head()

xxxxxxxxxx

 # Create a DataFrame that displays the mean value of the “daily_returns” columns for all four assets.etf_portfolio_returns = etf_portfolio["daily_returns"].mean(axis=1)​# Review the resulting DataFrameetf_portfolio_returns

0     -0.007038
1     -0.001216
2      0.008567
3     -0.001004
4     -0.008243
         ...   
994   -0.014635
995   -0.003990
996   -0.006288
997    0.011246
998    0.009108
Length: 999, dtype: float64

xxxxxxxxxx

 # Use the average daily returns provided by the etf_portfolio_returns DataFrame # to calculate the annualized return for the portfolio. year_trading_days = 252annualized_etf_portfolio_returns = etf_portfolio_returns.mean() * year_trading_days * 100​# Display the annualized return value of the ETF portfolio.print(f'The annualized return value of the ETF portfolio is {annualized_etf_portfolio_returns:.2f}%')

The annualized return value of the ETF portfolio is 43.83%

xxxxxxxxxx

 # Use the average daily returns provided by the etf_portfolio_returns DataFrame to calculate the cumulative returnsetf_cumulative_returns = ((1+ etf_portfolio_returns).cumprod())​# Display the final cumulative return valueetf_cumulative_returns.head()

0    0.992962
1    0.991755
2    1.000251
3    0.999246
4    0.991010
dtype: float64

xxxxxxxxxx ### Step 5: Using hvPlot, create an interactive line plot that visualizes the cumulative return values of the ETF portfolio. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.

 # Using hvplot, create an interactive line plot that visualizes the ETF portfolios cumulative return values.etf_cumulative_returns.hvplot(    x='time',    title= "ETF Cumulative Returns",    height= 400,     width= 800,)

 # Import the required libraries and dependenciesimport pandas as pdimport hvplot.pandasfrom pathlib import Path

xxxxxxxxxx## Import the data

 # Using the read_csv function and Path module, create a DataFrame # by importing the sfo_neighorhobods_census_data.csv file from the Resources foldersfo_data_df = pd.read_csv(    Path("Resources/sfo_neighborhoods_census_data.csv"))​# Review the first and last five rows of the DataFramedisplay(sfo_data_df.head())display(sfo_data_df.tail())

xxxxxxxxxx---

xxxxxxxxxx## Calculate and Plot the Housing Units per Year

xxxxxxxxxx### Step 1: Use the `groupby` function to group the data by year. Aggregate the results by the `mean` of the groups.

 # Create a numerical aggregation that groups the data by the year and then averages the results.housing_units_year=sfo_data_df.groupby('year').mean() # Review the DataFramehousing_units_year

xxxxxxxxxx### Step 2: Use the `hvplot` function to plot the `housing_units_by_year` DataFrame as a bar chart. Make the x-axis represent the `year` and the y-axis represent the `housing_units`.

 # Create a visual aggregation explore the housing units by yearhousing_units_year.hvplot.bar(    x='year',    y='housing_units',    xlabel="Year",     ylabel="Housing_Units",    title=('Housing Units in San Francisco from 2010 to 2016'),    ylim=(365000, 385000)).opts(    yformatter='%.0f'    )

xxxxxxxxxx### Step 5: Answer the following question:

xxxxxxxxxx**Question:** What is the overall trend in housing_units over the period being analyzed?

xxxxxxxxxx---

xxxxxxxxxx## Calculate and Plot the Average Sale Prices per Square Foot

xxxxxxxxxx### Step 1: Group the data by year, and then average the results.

 # Create a numerical aggregation by grouping the data by year and averaging the resultsgroup_df = sfo_data_df.groupby('year').mean() # Review the resulting DataFramegroup_df

xxxxxxxxxx**Question:** What is the lowest gross rent reported for the years included in the DataFrame?

xxxxxxxxxx### Step 2: Create a new DataFrame named `prices_square_foot_by_year` by filtering out the “housing_units” column. The new DataFrame should include the averages per year for only the sale price per square foot and the gross rent.

 # Filter out the housing_units column, creating a new DataFrame # Keep only sale_price_sqr_foot and gross_rent averages per year​prices_square_foot_by_year = group_df[['sale_price_sqr_foot','gross_rent']]                                          # Review the DataFrameprices_square_foot_by_year

xxxxxxxxxx### Step 3: Use hvPlot to plot the `prices_square_foot_by_year` DataFrame as a line plot.

 # Plot prices_square_foot_by_year. # Inclued labels for the x- and y-axes, and a title.prices_square_foot_by_year.hvplot(    x='year',    y= ['gross_rent', 'sale_price_sqr_foot'],    xlabel="Year",     ylabel="Gross Rent / Sale Price Per Square Foot ",    title=('Housing Units in San Francisco from 2010 to 2016'),    figsize=(10,5)).opts(    yformatter='%.0f')

xxxxxxxxxx### Step 6: Use both the `prices_square_foot_by_year` DataFrame and interactive plots to answer the following questions:

xxxxxxxxxx**Question:** Did any year experience a drop in the average sale price per square foot compared to the previous year?

xxxxxxxxxx**Question:** If so, did the gross rent increase or decrease during that year?

xxxxxxxxxx---

xxxxxxxxxx## Compare the Average Sale Prices by Neighborhood

xxxxxxxxxx### Step 1: Create a new DataFrame that groups the original DataFrame by year and neighborhood. Aggregate the results by the `mean` of the groups.

 # Group by year and neighborhood and then create a new dataframe of the mean valuesprices_by_year_by_neighborhood = sfo_data_df.groupby(['year', 'neighborhood']).mean()​# Review the DataFrameprices_by_year_by_neighborhood

xxxxxxxxxx### Step 2: Filter out the “housing_units” column to create a DataFrame that includes only the `sale_price_sqr_foot` and `gross_rent` averages per year.

 # Filter out the housing_unitsprices_by_year_by_neighborhood = prices_by_year_by_neighborhood.filter(    ['sale_price_sqr_foot', 'gross_rent'])​# Review the first and last five rows of the DataFramedisplay(prices_by_year_by_neighborhood.head())display(prices_by_year_by_neighborhood.tail())

xxxxxxxxxx### Step 3: Create an interactive line plot with hvPlot that visualizes both `sale_price_sqr_foot` and `gross_rent`. Set the x-axis parameter to the year (`x="year"`). Use the `groupby` parameter to create an interactive widget for `neighborhood`.

xxxxxxxxxx### Step 4: Style and format the line plot to ensure a professionally styled visualization.

 # Use hvplot to create an interactive line plot of the average price per square foot# The plot should have a dropdown selector for the neighborhoodprices_by_year_by_neighborhood.hvplot(    groupby='neighborhood',    x='year',    y= ['gross_rent','sale_price_sqr_foot'],    xlabel="Year",     ylabel="Sale Price Per Square Foot/Gross Rent",    figsize=(15,5),    title= 'Sale Price Per Square Foot and Average Gross Rent - 2010-2016 - By Neighborhood')

xxxxxxxxxx### Step 6: Use the interactive visualization to answer the following question:

xxxxxxxxxx**Question:** For the Anza Vista neighborhood, is the average sale price per square foot for 2016 more or less than the price that’s listed for 2012?

xxxxxxxxxx---

xxxxxxxxxx## Build an Interactive Neighborhood Map

xxxxxxxxxx### Step 1: Read the `neighborhood_coordinates.csv` file from the `Resources` folder into the notebook, and create a DataFrame named `neighborhood_locations_df`. Be sure to set the `index_col` of the DataFrame as “Neighborhood”.

 # Load neighborhoods coordinates dataneighborhood_locations_df = pd.read_csv(    Path("Resources/neighborhoods_coordinates.csv"),    index_col=("Neighborhood"))​# Review the DataFrameneighborhood_locations_df

xxxxxxxxxx### Step 2: Using the original `sfo_data_df` Dataframe, create a DataFrame named `all_neighborhood_info_df` that groups the data by neighborhood. Aggregate the results by the `mean` of the group.

 # Calculate the mean values for each neighborhoodall_neighborhood_info_df = sfo_data_df.groupby(['neighborhood']).mean()​# Review the resulting DataFrameall_neighborhood_info_df

xxxxxxxxxx### Step 3: Review the two code cells that concatenate the `neighborhood_locations_df` DataFrame with the `all_neighborhood_info_df` DataFrame.

 # Using the Pandas `concat` function, join the # neighborhood_locations_df and the all_neighborhood_info_df DataFrame# The axis of the concatenation is "columns".# The concat function will automatially combine columns with# identical information, while keeping the additional columns.all_neighborhoods_df = pd.concat(    [neighborhood_locations_df, all_neighborhood_info_df],     axis="columns",    sort=False)​# Review the resulting DataFrameall_neighborhoods_df

 # Call the dropna function to remove any neighborhoods that do not have dataall_neighborhoods_df = all_neighborhoods_df.reset_index().dropna()

 # Rename the "index" column as "Neighborhood" for use in the Visualizationall_neighborhoods_df= all_neighborhoods_df.rename(columns={"index": "Neighborhood"})

 # Review the resulting DataFrameall_neighborhoods_df

xxxxxxxxxx### Step 4: Using hvPlot with GeoViews enabled, create a `points` plot for the `all_neighborhoods_df` DataFrame. Be sure to do the following:

 # Create a plot to analyze neighborhood infoall_neighborhoods_df.hvplot.points(    'Lon',     'Lat',    geo=True,     size = 'sale_price_sqr_foot',    color='gross_rent',    alpha=0.8,    tiles='OSM',    frame_width = 700,    frame_height = 500,    hover_cols='Neighborhood')

xxxxxxxxxx### Step 5: Use the interactive map to answer the following question:

xxxxxxxxxx**Question:** Which neighborhood has the highest gross rent, and which has the highest sale price per square foot?

xxxxxxxxxx## Compose Your Data Story

xxxxxxxxxx**Question:** How does the trend in rental income growth compare to the trend in sales prices? Does this same trend hold true for all the neighborhoods across San Francisco?

xxxxxxxxxx# Financial Planning with APIs and Simulations

xxxxxxxxxx # Import the required libraries and dependenciesimport osimport requestsimport jsonimport pandas as pdfrom dotenv import load_dotenvimport alpaca_trade_api as tradeapifrom MCForecastTools import MCSimulation​%matplotlib inline

xxxxxxxxxx # Load the environment variables from the .env file# by calling the load_dotenv functionload_dotenv()

True

xxxxxxxxxx## Part 1: Create a Financial Planner for Emergencies

xxxxxxxxxx### Evaluate the Cryptocurrency Wallet by Using the Requests Library

xxxxxxxxxx # The current number of coins for each cryptocurrency asset held in the portfolio.btc_coins = 1.2eth_coins = 5.3

xxxxxxxxxx#### Step 1: Create a variable named `monthly_income`, and set its value to `12000`.

xxxxxxxxxx # The monthly amount for the member's household incomemonthly_income = 12000

xxxxxxxxxx#### Review the endpoint URLs for the API calls to Free Crypto API in order to get the current pricing information for both BTC and ETH.

xxxxxxxxxx # The Free Crypto API Call endpoint URLs for the held cryptocurrency assetsbtc_url = "https://api.alternative.me/v2/ticker/Bitcoin/?convert=USD"eth_url = "https://api.alternative.me/v2/ticker/Ethereum/?convert=USD"

xxxxxxxxxx#### Step 2. Use the Requests library to get the current price (in US dollars) of Bitcoin (BTC) and Ethereum (ETH) by using the API endpoints that the starter code supplied.

xxxxxxxxxx # Using the Python requests library, make an API call to access the current price of BTCbtc_response = requests.get(btc_url).json()​# Use the json.dumps function to review the response data from the API call# Use the indent and sort_keys parameters to make the response object readableprint(json.dumps(btc_response, indent=4, sort_keys=True))

{
    "data": {
        "1": {
            "circulating_supply": 18856225,
            "id": 1,
            "last_updated": 1635371610,
            "max_supply": 21000000,
            "name": "Bitcoin",
            "quotes": {
                "USD": {
                    "market_cap": 1113069757924,
                    "percent_change_1h": 0.418862862272553,
                    "percent_change_24h": -4.0950855132471,
                    "percent_change_7d": -8.40586549522251,
                    "percentage_change_1h": 0.418862862272553,
                    "percentage_change_24h": -4.0950855132471,
                    "percentage_change_7d": -8.40586549522251,
                    "price": 59094.0,
                    "volume_24h": 45963798340
                }
            },
            "rank": 1,
            "symbol": "BTC",
            "total_supply": 18856225,
            "website_slug": "bitcoin"
        }
    },
    "metadata": {
        "error": null,
        "num_cryptocurrencies": 3105,
        "timestamp": 1635371610
    }
}

xxxxxxxxxx # Using the Python requests library, make an API call to access the current price ETHeth_response = requests.get(eth_url).json()# Use the json.dumps function to review the response data from the API call# Use the indent and sort_keys parameters to make the response object readableprint(json.dumps(eth_response, indent=4, sort_keys=True))

{
    "data": {
        "1027": {
            "circulating_supply": 118102719,
            "id": 1027,
            "last_updated": 1635371594,
            "max_supply": 0,
            "name": "Ethereum",
            "quotes": {
                "USD": {
                    "market_cap": 473585808743,
                    "percent_change_1h": 0.719398483949307,
                    "percent_change_24h": -5.75624760377625,
                    "percent_change_7d": 3.1730415665463,
                    "percentage_change_1h": 0.719398483949307,
                    "percentage_change_24h": -5.75624760377625,
                    "percentage_change_7d": 3.1730415665463,
                    "price": 4007.85,
                    "volume_24h": 30002251517
                }
            },
            "rank": 2,
            "symbol": "ETH",
            "total_supply": 118102719,
            "website_slug": "ethereum"
        }
    },
    "metadata": {
        "error": null,
        "num_cryptocurrencies": 3105,
        "timestamp": 1635371594
    }
}

xxxxxxxxxx#### Step 3: Navigate the JSON response object to access the current price of each coin, and store each in a variable.

xxxxxxxxxx # Navigate the BTC response object to access the current price of BTCbtc_price = btc_response['data']['1']['quotes']['USD']['price']​# Print the current price of BTCprint(f'The current price of BTC is ${btc_price:.2f}')

The current price of BTC is $59094.00

xxxxxxxxxx # Navigate the BTC response object to access the current price of ETHeth_price = eth_response['data']['1027']['quotes']['USD']['price']​# Print the current price of ETHprint(f'The current price of BTC is ${eth_price:.2f}')

The current price of BTC is $4007.85

xxxxxxxxxx### Step 4: Calculate the value, in US dollars, of the current amount of each cryptocurrency and of the entire cryptocurrency wallet.

x# Compute the current value of the BTC holding btc_value = btc_coins * btc_price​# Print current value of your holding in BTCprint(f'The current value of the BTC holding is ${btc_value:.2f}')

The current value of the BTC holding is $70912.80

xxxxxxxxxx # Compute the current value of the ETH holding eth_value = eth_coins * eth_price​# Print current value of your holding in ETHprint(f'The current value of the ETH holding is ${eth_value:.2f}')

The current value of the ETH holding is $21241.60

xxxxxxxxxx # Compute the total value of the cryptocurrency wallet# Add the value of the BTC holding to the value of the ETH holdingtotal_crypto_wallet = btc_value + eth_value​# Print current cryptocurrency wallet balanceprint(f'The current cryptocurrency wallet balance is ${total_crypto_wallet:.2f}')

The current cryptocurrency wallet balance is $92154.40

xxxxxxxxxx### Evaluate the Stock and Bond Holdings by Using the Alpaca SDK

xxxxxxxxxx#### Review the total number of shares held in both (SPY) and (AGG).

xxxxxxxxxx # Current amount of shares held in both the stock (SPY) and bond (AGG) portion of the portfolio.spy_shares = 110agg_shares = 200​

xxxxxxxxxx#### Step 1: In the `Starter_Code` folder, create an environment file (`.env`) to store the values of your Alpaca API key and Alpaca secret key.

xxxxxxxxxx#### Step 2: Set the variables for the Alpaca API and secret keys. Using the Alpaca SDK, create the Alpaca `tradeapi.REST` object. In this object, include the parameters for the Alpaca API key, the secret key, and the version number.

xxxxxxxxxx # Set the variables for the Alpaca API and secret keysalpaca_api_key = os.getenv("ALPACA_API_KEY")alpaca_secret_key = os.getenv("ALPACA_SECRET_KEY")​# Create the Alpaca tradeapi.REST objectalpaca = tradeapi.REST(    alpaca_api_key,    alpaca_secret_key,    api_version="v2")

xxxxxxxxxx#### Step 3: Set the following parameters for the Alpaca API call:

xxxxxxxxxx # Set the tickers for both the bond and stock portion of the portfoliotickers = ['SPY', 'AGG']​# Set timeframe to 1D timeframe = "1D"​# Format current date as ISO format# Set both the start and end date at the date of your prior weekday # This will give you the closing price of the previous trading day# Alternatively you can use a start and end date of 2020-08-07start_date = pd.Timestamp("2021-10-21", tz="America/New_York").isoformat()end_date = pd.Timestamp("2021-10-21", tz="America/New_York").isoformat()

xxxxxxxxxx#### Step 4: Get the current closing prices for `SPY` and `AGG` by using the Alpaca `get_barset` function. Format the response as a Pandas DataFrame by including the `df` property at the end of the `get_barset` function.

xxxxxxxxxx # Use the Alpaca get_barset function to get current closing prices the portfolio# Be sure to set the `df` property after the function to format the response object as a DataFramedf_portfolio = alpaca.get_barset(    tickers,    timeframe,    start = start_date,    end = end_date).df​# Review the first 5 rows of the Alpaca DataFramedf_portfolio.head()

xxxxxxxxxx#### Step 5: Navigating the Alpaca response DataFrame, select the `SPY` and `AGG` closing prices, and store them as variables.

xxxxxxxxxx # Access the closing price for AGG from the Alpaca DataFrame# Converting the value to a floating point numberagg_close_price = float(df_portfolio['AGG']['close'])# Print the AGG closing priceprint(f'The AGG closing price is: ${agg_close_price:.2f}')

The AGG closing price is: $113.85

xxxxxxxxxx # Access the closing price for SPY from the Alpaca DataFrame# Converting the value to a floating point numberspy_close_price = float(df_portfolio['SPY']['close']) ​# Print the SPY closing priceprint(f'The SPY closing price is: ${spy_close_price:.2f}')

The SPY closing price is: $453.61

xxxxxxxxxx#### Step 6: Calculate the value, in US dollars, of the current amount of shares in each of the stock and bond portions of the portfolio, and print the results.

xxxxxxxxxx # Calculate the current value of t"he bond portion of the portfolioagg_value = agg_close_price * agg_shares​# Print the current value of the bond portfolioprint(f'The current value of the AGG Bond portfolio is: ${agg_value:.2f}')

The current value of the AGG Bond portfolio is: $22770.00

xxxxxxxxxx # Calculate the current value of the stock portion of the portfoliospy_value = spy_shares * spy_close_price # Print the current value of the stock portfolioprint(f'The current value of the stock portion of the portfolio is: ${spy_value:.2f}')

The current value of the stock portion of the portfolio is: $49897.10

xxxxxxxxxx # Calculate the total value of the stock and bond portion of the portfoliototal_stocks_bonds = agg_value + spy_value ​# Print the current balance of the stock and bond portion of the portfolioprint(f'The current balance of the stock and bond portion of the portfolio is: ${total_stocks_bonds:.2f}')

The current balance of the stock and bond portion of the portfolio is: $72667.10

xxxxxxxxxx # Calculate the total value of the member's entire savings portfolio# Add the value of the cryptocurrency walled to the value of the total stocks and bondstotal_portfolio = total_stocks_bonds + total_crypto_wallet ​# Print current cryptocurrency wallet balanceprint(f'The total value of the entire savings portfolio is: ${total_portfolio:.2f}')

The total value of the entire savings portfolio is: $164821.51

xxxxxxxxxx### Evaluate the Emergency Fund

xxxxxxxxxx#### Step 1: Create a Python list named `savings_data` that has two elements. The first element contains the total value of the cryptocurrency wallet. The second element contains the total value of the stock and bond portions of the portfolio.

xxxxxxxxxx # Consolidate financial assets data into a Python listsavings_data = [total_crypto_wallet, total_stocks_bonds]​# Review the Python list savings_datadisplay(savings_data)

[92154.405, 72667.1]

xxxxxxxxxx#### Step 2: Use the `savings_data` list to create a Pandas DataFrame named `savings_df`, and then display this DataFrame. The function to create the DataFrame should take the following three parameters:

xxxxxxxxxx # Create a Pandas DataFrame called savings_df ​savings_df = pd.DataFrame(savings_data,    columns = ['Amount'],    index = ['Crypto Value', 'Stocks/Bonds Value'])# Display the savings_df DataFramesavings_df

xxxxxxxxxx#### Step 3: Use the `savings_df` DataFrame to plot a pie chart that visualizes the composition of the member’s portfolio. The y-axis of the pie chart uses `amount`. Be sure to add a title.

xxxxxxxxxx # Plot the total value of the member's portfolio (crypto and stock/bond) in a pie chart​savings_df.plot(    kind="pie",    title="Total Value of My Portfolio",    subplots=True,    figsize=(10,5))

array([<AxesSubplot:ylabel='Amount'>], dtype=object)

xxxxxxxxxx#### Step 4: Using Python, determine if the current portfolio has enough to create an emergency fund as part of the member’s financial plan. Ideally, an emergency fund should equal to three times the member’s monthly income. To do this, implement the following steps:

xxxxxxxxxx##### Step 4-1: Create a variable named `emergency_fund_value`, and set it equal to three times the value of the member’s `monthly_income` of 12000. (You set this earlier in Part 1).

xxxxxxxxxx # Create a variable named emergency_fund_valueemergency_fund_value = monthly_income * 3print(f'The emergency fund value is: ${emergency_fund_value:.2f}')

The emergency fund value is: $36000.00

xxxxxxxxxx##### Step 4-2: Create a series of three if statements to determine if the member’s total portfolio is large enough to fund the emergency portfolio:

xxxxxxxxxx # Evaluate the possibility of creating an emergency fund with 3 conditions:if total_portfolio > emergency_fund_value:    print(f'Congratulations for having enough money in this fund!')elif total_portfolio==emergency_fund_value:    print(f'Congratulations on reaching this important financial goal!')else:    print(f'You are ${emergency_fund_value-total_portfolio} from reaching the goal.')

Congratulations for having enough money in this fund!

xxxxxxxxxx## Part 2: Create a Financial Planner for Retirement

xxxxxxxxxx### Create the Monte Carlo Simulation

xxxxxxxxxx#### Step 1: Make an API call via the Alpaca SDK to get 3 years of historical closing prices for a traditional 60/40 portfolio split: 60% stocks (SPY) and 40% bonds (AGG).

xxxxxxxxxx # Set start and end dates of 3 years back from your current date# Alternatively, you can use an end date of 2020-08-07 and work 3 years back from that date start_date = "2018-10-21" end_date = "2021-10-21"​# Set number of rows to 1000 to retrieve the maximum amount of rowslimit_rows=1000

xxxxxxxxxx # Use the Alpaca get_barset function to make the API call to get the 3 years worth of pricing data# The tickers and timeframe parameters should have been set in Part 1 of this activity # The start and end dates should be updated with the information set above# Remember to add the df property to the end of the call so the response is returned as a DataFramethree_yr_portfolio = alpaca.get_barset(    tickers,    timeframe,    start = start_date,     end = end_date).df​# Display both the first and last five rows of the DataFramedisplay(three_yr_portfolio.head())display(three_yr_portfolio.tail())

xxxxxxxxxx#### Step 2: Run a Monte Carlo simulation of 500 samples and 30 years for the 60/40 portfolio, and then plot the results.

xxxxxxxxxx # Configure the Monte Carlo simulation to forecast 30 years cumulative returns# The weights should be split 40% to AGG and 60% to SPY.# Run 500 samples.thirty_year_simulation = MCSimulation(    portfolio_data = three_yr_portfolio,    weights = [0.40, 0.60],    num_simulation = 500,    num_trading_days = 252 * 30,)# Review the simulation input datathirty_year_simulation.portfolio_data.head()# Set number of simulationsnum_sims = 500

xxxxxxxxxx # Run the Monte Carlo simulation to forecast 30 years cumulative returnsthirty_year_simulation.calc_cumulative_return()

Running Monte Carlo simulation number 0.
Running Monte Carlo simulation number 10.
Running Monte Carlo simulation number 20.
Running Monte Carlo simulation number 30.
Running Monte Carlo simulation number 40.
Running Monte Carlo simulation number 50.
Running Monte Carlo simulation number 60.
Running Monte Carlo simulation number 70.
Running Monte Carlo simulation number 80.
Running Monte Carlo simulation number 90.
Running Monte Carlo simulation number 100.
Running Monte Carlo simulation number 110.
Running Monte Carlo simulation number 120.
Running Monte Carlo simulation number 130.
Running Monte Carlo simulation number 140.
Running Monte Carlo simulation number 150.
Running Monte Carlo simulation number 160.
Running Monte Carlo simulation number 170.
Running Monte Carlo simulation number 180.
Running Monte Carlo simulation number 190.
Running Monte Carlo simulation number 200.
Running Monte Carlo simulation number 210.
Running Monte Carlo simulation number 220.
Running Monte Carlo simulation number 230.
Running Monte Carlo simulation number 240.
Running Monte Carlo simulation number 250.
Running Monte Carlo simulation number 260.
Running Monte Carlo simulation number 270.
Running Monte Carlo simulation number 280.
Running Monte Carlo simulation number 290.
Running Monte Carlo simulation number 300.
Running Monte Carlo simulation number 310.
Running Monte Carlo simulation number 320.
Running Monte Carlo simulation number 330.
Running Monte Carlo simulation number 340.
Running Monte Carlo simulation number 350.
Running Monte Carlo simulation number 360.
Running Monte Carlo simulation number 370.
Running Monte Carlo simulation number 380.
Running Monte Carlo simulation number 390.
Running Monte Carlo simulation number 400.
Running Monte Carlo simulation number 410.
Running Monte Carlo simulation number 420.
Running Monte Carlo simulation number 430.
Running Monte Carlo simulation number 440.
Running Monte Carlo simulation number 450.
Running Monte Carlo simulation number 460.
Running Monte Carlo simulation number 470.
Running Monte Carlo simulation number 480.
Running Monte Carlo simulation number 490.

xxxxxxxxxx ### Visualize the 30-year Monte Carlo simulation by creating an# overlay line plotthirty_year_simulation.plot_simulation()

<AxesSubplot:title={'center':'500 Simulations of Cumulative Portfolio Return Trajectories Over the Next 7560 Trading Days.'}>

xxxxxxxxxx#### Step 3: Plot the probability distribution of the Monte Carlo simulation.

xxxxxxxxxx # Visualize the probability distribution of the 30-year Monte Carlo simulation # by plotting a histogramthirty_year_simulation.plot_distribution()

<AxesSubplot:title={'center':'Distribution of Final Cumuluative Returns Across All 500 Simulations'}, ylabel='Frequency'>

xxxxxxxxxx#### Step 4: Generate the summary statistics for the Monte Carlo simulation.

xxxxxxxxxx # Generate summary statistics from the 30-year Monte Carlo simulation results# Save the results as a variablethirty_tbl=thirty_year_simulation.summarize_cumulative_return()​# Review the 30-year Monte Carlo summary statisticsthirty_tbl

count           500.000000
mean             31.158117
std              11.825937
min               9.948483
25%              23.042305
50%              29.168570
75%              36.910780
max              76.927008
95% CI Lower     14.285925
95% CI Upper     62.288960
Name: 7560, dtype: float64

xxxxxxxxxx### Analyze the Retirement Portfolio Forecasts

xxxxxxxxxx # Print the current balance of the stock and bond portion of the members portfolioprint(f'The current balance of the stock and bond portion of the portfolio is: ${total_stocks_bonds:.2f}')

The current balance of the stock and bond portion of the portfolio is: $72667.10

xxxxxxxxxx # Use the lower and upper `95%` confidence intervals to calculate the range of the possible outcomes for the current stock/bond portfolioci_lower_thirty_cumulative_return = round(thirty_tbl[8] * total_stocks_bonds,2)ci_upper_thirty_cumulative_return = round(thirty_tbl[9] * total_stocks_bonds,2)​# Print the result of your calculationsprint(f"There is a 95% chance that your current stock/bond portfolio"      f" over the next year will end within in the range of"      f" ${ci_lower_thirty_cumulative_return} and ${ci_upper_thirty_cumulative_return}.")

There is a 95% chance that your current stock/bond portfolio over the next year will end within in the range of $1038116.74 and $4526358.08.

xxxxxxxxxx### Forecast Cumulative Returns in 10 Years

xxxxxxxxxx # Configure a Monte Carlo simulation to forecast 10 years cumulative returns# The weights should be split 20% to AGG and 80% to SPY.# Run 500 samples.ten_year_simulation = MCSimulation(    portfolio_data=three_yr_portfolio,    weights=[0.20, 0.80],    num_simulation=500,    num_trading_days=252*10,)# Review the simulation input dataten_year_simulation.portfolio_data.head()

xxxxxxxxxx # Run the Monte Carlo simulation to forecast 10 years cumulative returnsten_year_simulation.calc_cumulative_return()

Running Monte Carlo simulation number 0.
Running Monte Carlo simulation number 10.
Running Monte Carlo simulation number 20.
Running Monte Carlo simulation number 30.
Running Monte Carlo simulation number 40.
Running Monte Carlo simulation number 50.
Running Monte Carlo simulation number 60.
Running Monte Carlo simulation number 70.
Running Monte Carlo simulation number 80.
Running Monte Carlo simulation number 90.
Running Monte Carlo simulation number 100.
Running Monte Carlo simulation number 110.
Running Monte Carlo simulation number 120.
Running Monte Carlo simulation number 130.
Running Monte Carlo simulation number 140.
Running Monte Carlo simulation number 150.
Running Monte Carlo simulation number 160.
Running Monte Carlo simulation number 170.
Running Monte Carlo simulation number 180.
Running Monte Carlo simulation number 190.
Running Monte Carlo simulation number 200.
Running Monte Carlo simulation number 210.
Running Monte Carlo simulation number 220.
Running Monte Carlo simulation number 230.
Running Monte Carlo simulation number 240.
Running Monte Carlo simulation number 250.
Running Monte Carlo simulation number 260.
Running Monte Carlo simulation number 270.
Running Monte Carlo simulation number 280.
Running Monte Carlo simulation number 290.
Running Monte Carlo simulation number 300.
Running Monte Carlo simulation number 310.
Running Monte Carlo simulation number 320.
Running Monte Carlo simulation number 330.
Running Monte Carlo simulation number 340.
Running Monte Carlo simulation number 350.
Running Monte Carlo simulation number 360.
Running Monte Carlo simulation number 370.
Running Monte Carlo simulation number 380.
Running Monte Carlo simulation number 390.
Running Monte Carlo simulation number 400.
Running Monte Carlo simulation number 410.
Running Monte Carlo simulation number 420.
Running Monte Carlo simulation number 430.
Running Monte Carlo simulation number 440.
Running Monte Carlo simulation number 450.
Running Monte Carlo simulation number 460.
Running Monte Carlo simulation number 470.
Running Monte Carlo simulation number 480.
Running Monte Carlo simulation number 490.

xxxxxxxxxx # Visualize the 10-year Monte Carlo simulation by creating an# overlay line plotten_year_simulation.plot_simulation()

<AxesSubplot:title={'center':'500 Simulations of Cumulative Portfolio Return Trajectories Over the Next 2520 Trading Days.'}>

xxxxxxxxxx # Visualize the probability distribution of the 10-year Monte Carlo simulation # by plotting a histogramten_year_simulation.plot_distribution()​

<AxesSubplot:title={'center':'Distribution of Final Cumuluative Returns Across All 500 Simulations'}, ylabel='Frequency'>

xxxxxxxxxx # Generate summary statistics from the 10-year Monte Carlo simulation results# Save the results as a variableten_tbl=ten_year_simulation.summarize_cumulative_return()​​# Review the 10-year Monte Carlo summary statisticsten_tbl

count           500.000000
mean              4.507405
std               1.283335
min               2.048672
25%               3.552285
50%               4.338490
75%               5.297036
max               9.637028
95% CI Lower      2.458394
95% CI Upper      7.593682
Name: 2520, dtype: float64

xxxxxxxxxx### Answer the following questions:

xxxxxxxxxx#### Question: Using the current value of only the stock and bond portion of the member's portfolio and the summary statistics that you generated from the new Monte Carlo simulation, what are the lower and upper bounds for the expected value of the portfolio (with the new weights) with a 95% confidence interval?

xxxxxxxxxx # Print the current balance of the stock and bond portion of the members portfolioprint(f'The current balance of the stock and bond portion of the portfolio is: ${total_stocks_bonds:.2f}')

The current balance of the stock and bond portion of the portfolio is: $72667.10

xxxxxxxxxx # Use the lower and upper `95%` confidence intervals to calculate the range of the possible outcomes for the current stock/bond portfolioci_lower_ten_cumulative_return = round(ten_tbl[8] * total_stocks_bonds,2)ci_upper_ten_cumulative_return = round(ten_tbl[9] * total_stocks_bonds,2)​# Print the result of your calculationsprint(f"There is a 95% chance that your current stock/bond portfolio"      f" over the next ten years will end within in the range of"      f" ${ci_lower_ten_cumulative_return} and ${ci_upper_ten_cumulative_return}.")​

There is a 95% chance that your current stock/bond portfolio over the next ten years will end within in the range of $178644.35 and $551810.81.

xxxxxxxxxx#### Question: Will weighting the portfolio more heavily to stocks allow the credit union members to retire after only 10 years?

xxxxxxxxxx **Answer** It depends on the credit union member's age, life expectancy and lifestyle. Based on the summary statistics for the ten year analysis, the current portfolio has a 95% probability to grow 2.89% to 8.63% versus 95% probability to grow 17.46% to 71.11% for the 30 year analysis.

	Announced	Period	Amount	Yield	Ex-Dividend	Record Date	Payable Date
0	9/14/2021	quarterly	$0.62	0.83%	11/17/2021	11/18/2021	12/9/2021
1	6/16/2021	quarterly	$0.56	0.87%	8/18/2021	8/19/2021	9/9/2021
2	3/16/2021	quarterly	$0.56	0.94%	5/19/2021	5/20/2021	6/10/2021
3	12/2/2020	quarterly	$0.56	1.04%	2/17/2021	2/18/2021	3/11/2021
4	9/15/2020	quarterly	$0.56	1.07%	11/18/2020	11/19/2020	12/10/2020

	MSFT
	open	high	low	close	volume
time
2017-11-09 00:00:00-05:00	84.11	84.270	82.90	84.09	16833031
2017-11-10 00:00:00-05:00	83.79	84.095	83.23	83.86	13902816
2017-11-13 00:00:00-05:00	83.66	83.940	83.46	83.93	9097109
2017-11-14 00:00:00-05:00	83.50	84.100	82.98	84.05	15246771
2017-11-15 00:00:00-05:00	83.47	83.690	82.69	82.99	14184790

	MSFT
	open	high	low	close	volume
time
2021-10-25 00:00:00-04:00	309.54	309.5400	306.4600	308.30	13455241
2021-10-26 00:00:00-04:00	310.81	312.3950	308.5967	309.71	20748654
2021-10-27 00:00:00-04:00	316.00	326.0973	316.0000	323.14	45222243
2021-10-28 00:00:00-04:00	324.32	324.8700	321.3601	324.32	21324602
2021-10-29 00:00:00-04:00	324.14	332.0000	323.9000	331.64	26504382

	MSFT
	open	high	low	close	volume	daily_return
time
2017-11-09 00:00:00-05:00	84.11	84.270	82.90	84.09	16833031	NaN
2017-11-10 00:00:00-05:00	83.79	84.095	83.23	83.86	13902816	-0.002735
2017-11-13 00:00:00-05:00	83.66	83.940	83.46	83.93	9097109	0.000835
2017-11-14 00:00:00-05:00	83.50	84.100	82.98	84.05	15246771	0.001430
2017-11-15 00:00:00-05:00	83.47	83.690	82.69	82.99	14184790	-0.012612

	open	high	low	close	volume	daily_returns
time
2016-12-16	39.90	39.90	39.12	39.32	7298861	-0.005564
2016-12-19	39.40	39.80	39.11	39.45	3436478	0.003306
2016-12-20	39.61	39.74	39.26	39.74	2940991	0.007351
2016-12-21	39.84	40.74	39.82	40.09	5826704	0.008807
2016-12-22	40.04	40.09	39.54	39.68	4338385	-0.010227

	open	high	low	close	volume	daily_returns
time
2020-11-30	212.51	215.83	207.0900	214.200	8992681	0.013629
2020-12-01	217.15	220.57	214.3401	216.520	9148174	0.010831
2020-12-02	215.60	215.75	210.5000	212.660	6414746	-0.017827
2020-12-03	213.33	216.93	213.1100	214.680	6463339	0.009499
2020-12-04	214.88	217.28	213.0100	217.235	2118319	0.011901

	time	close
0	2020-08-05 00:00:00.000000	202.920
1	2020-08-06 00:00:00.000000	204.090
2	2020-08-25 00:00:00.000000	201.710
3	2020-08-26 00:00:00.000000	203.530
4	2020-08-27 00:00:00.000000	204.340
5	2020-08-28 00:00:00.000000	204.480
6	2020-08-31 00:00:00.000000	203.950
7	2020-09-01 00:00:00.000000	208.920
8	2020-09-02 00:00:00.000000	210.820
9	2020-09-03 00:00:00.000000	205.070
10	2020-10-12 00:00:00.000000	201.500
11	2020-10-13 00:00:00.000000	207.730
12	2020-10-14 00:00:00.000000	203.660
13	2020-10-15 00:00:00.000000	203.170
14	2020-10-16 00:00:00.000000	204.540
15	2020-10-19 00:00:00.000000	200.050
16	2020-10-20 00:00:00.000000	201.960
17	2020-10-21 00:00:00.000000	213.060
18	2020-10-22 00:00:00.000000	203.880
19	2020-10-23 00:00:00.000000	203.040
20	2020-10-27 00:00:00.000000	200.440
21	2020-11-05 00:00:00.000000	204.590
22	2020-11-06 00:00:00.000000	202.720
23	2020-11-23 00:00:00.000000	200.820
24	2020-11-24 00:00:00.000000	206.000
25	2020-11-25 00:00:00.000000	214.380
26	2020-11-27 00:00:00.000000	211.320
27	2020-11-30 00:00:00.000000	214.200
28	2020-12-01 00:00:00.000000	216.520
29	2020-12-02 00:00:00.000000	212.660
30	2020-12-03 00:00:00.000000	214.680
31	2020-12-04 00:00:00.000000	217.235

	time	daily_returns
0	2020-03-24 00:00:00.000000	0.140981
1	2020-05-07 00:00:00.000000	0.140318
2	2020-03-13 00:00:00.000000	0.138700
3	2020-04-06 00:00:00.000000	0.100877
4	2018-10-19 00:00:00.000000	0.093371
5	2019-10-24 00:00:00.000000	0.085912
6	2020-11-04 00:00:00.000000	0.080986
7	2020-03-10 00:00:00.000000	0.080863
8	2020-04-22 00:00:00.000000	0.075321
9	2018-12-26 00:00:00.000000	0.074656

	time	open	high	low	close	volume	daily_returns	time	open	high	...	close	volume	daily_returns	time	open	high	low	close	volume	daily_returns
0	2016-12-16 00:00:00.000000	24.41	24.73	23.94	23.980	483544	-0.023218	2016-12-16 00:00:00.000000	242.80	243.19	...	39.32	7298861	-0.005564	2016-12-16 00:00:00.000000	14.29	14.47	14.2300	14.375	4516341	0.017339
1	2016-12-19 00:00:00.000000	24.00	24.01	23.55	23.790	288149	-0.007923	2016-12-19 00:00:00.000000	238.34	239.74	...	39.45	3436478	0.003306	2016-12-19 00:00:00.000000	14.34	14.60	14.3000	14.360	3944657	-0.001043
2	2016-12-20 00:00:00.000000	23.75	23.94	23.58	23.820	220341	0.001261	2016-12-20 00:00:00.000000	240.52	243.65	...	39.74	2940991	0.007351	2016-12-20 00:00:00.000000	14.73	14.82	14.4100	14.490	5207412	0.009053
3	2016-12-21 00:00:00.000000	23.90	23.97	23.69	23.860	249189	0.001679	2016-12-21 00:00:00.000000	242.24	242.40	...	40.09	5826704	0.008807	2016-12-21 00:00:00.000000	14.45	14.54	14.2701	14.380	3901738	-0.007591
4	2016-12-22 00:00:00.000000	23.90	24.01	23.70	24.005	383139	0.006077	2016-12-22 00:00:00.000000	241.23	242.86	...	39.68	4338385	-0.010227	2016-12-22 00:00:00.000000	14.33	14.34	13.9301	14.040	3874004	-0.023644

	year	neighborhood	sale_price_sqr_foot	housing_units	gross_rent
0	2010	Alamo Square	291.182945	372560	1239
1	2010	Anza Vista	267.932583	372560	1239
2	2010	Bayview	170.098665	372560	1239
3	2010	Buena Vista Park	347.394919	372560	1239
4	2010	Central Richmond	319.027623	372560	1239

	year	neighborhood	sale_price_sqr_foot	housing_units	gross_rent
392	2016	Telegraph Hill	903.049771	384242	4390
393	2016	Twin Peaks	970.085470	384242	4390
394	2016	Van Ness/ Civic Center	552.602567	384242	4390
395	2016	Visitacion Valley	328.319007	384242	4390
396	2016	Westwood Park	631.195426	384242	4390

	sale_price_sqr_foot	housing_units	gross_rent
year
2010	369.344353	372560	1239
2011	341.903429	374507	1530
2012	399.389968	376454	2324
2013	483.600304	378401	2971
2014	556.277273	380348	3528
2015	632.540352	382295	3739
2016	697.643709	384242	4390

		sale_price_sqr_foot	housing_units	gross_rent
year	neighborhood
2010	Alamo Square	291.182945	372560	1239
	Anza Vista	267.932583	372560	1239
	Bayview	170.098665	372560	1239
	Buena Vista Park	347.394919	372560	1239
	Central Richmond	319.027623	372560	1239
...	...	...	...	...
2016	Telegraph Hill	903.049771	384242	4390
	Twin Peaks	970.085470	384242	4390
	Van Ness/ Civic Center	552.602567	384242	4390
	Visitacion Valley	328.319007	384242	4390
	Westwood Park	631.195426	384242	4390

	Lat	Lon
Neighborhood
Alamo Square	37.791012	-122.402100
Anza Vista	37.779598	-122.443451
Bayview	37.734670	-122.401060
Bayview Heights	37.728740	-122.410980
Bernal Heights	37.728630	-122.443050
...	...	...
West Portal	37.740260	-122.463880
Western Addition	37.792980	-122.435790
Westwood Highlands	37.734700	-122.456854
Westwood Park	37.734150	-122.457000
Yerba Buena	37.792980	-122.396360

	year	sale_price_sqr_foot	housing_units	gross_rent
neighborhood
Alamo Square	2013.000000	366.020712	378401.00	2817.285714
Anza Vista	2013.333333	373.382198	379050.00	3031.833333
Bayview	2012.000000	204.588623	376454.00	2318.400000
Bayview Heights	2015.000000	590.792839	382295.00	3739.000000
Bernal Heights	2013.500000	576.746488	379374.50	3080.333333
...	...	...	...	...
West Portal	2012.250000	498.488485	376940.75	2515.500000
Western Addition	2012.500000	307.562201	377427.50	2555.166667
Westwood Highlands	2012.000000	533.703935	376454.00	2250.500000
Westwood Park	2015.000000	687.087575	382295.00	3959.000000
Yerba Buena	2012.500000	576.709848	377427.50	2555.166667

	AGG					SPY
	open	high	low	close	volume	open	high	low	close	volume
time
2021-06-08 00:00:00-04:00	114.84	114.87	114.7800	114.82	3951702	423.11	423.21	420.32	422.27	40131441
2021-06-09 00:00:00-04:00	115.43	115.43	114.9700	115.05	3418086	423.18	423.26	421.41	421.62	38368598
2021-06-10 00:00:00-04:00	115.30	115.31	114.8605	115.30	3665934	422.96	424.63	421.55	423.58	43577329
2021-06-11 00:00:00-04:00	115.31	115.32	115.1600	115.24	3476373	424.19	424.42	422.82	424.31	37018077
2021-06-14 00:00:00-04:00	115.18	115.18	114.9350	114.97	2999623	424.43	425.29	423.10	425.29	33128340

	AGG					SPY
	open	high	low	close	volume	open	high	low	close	volume
time
2021-10-21 00:00:00-04:00	114.01	114.0400	113.8423	113.85	4741154	451.76	453.829	451.31	453.61	33945007
2021-10-22 00:00:00-04:00	113.97	114.1399	113.8950	114.07	3309637	453.13	454.670	451.05	453.13	50771701
2021-10-25 00:00:00-04:00	114.06	114.2700	114.0600	114.17	7088133	454.28	455.900	452.39	455.48	37918548
2021-10-26 00:00:00-04:00	114.25	114.3800	114.1100	114.37	5284755	457.20	458.490	455.56	455.97	49865704
2021-10-27 00:00:00-04:00	114.66	114.9150	114.4250	114.77	6817074	456.45	457.160	453.86	453.92	48219255

	0	1	2	3	4	5	6	7	8	9	...	490	491	492	493	494	495	496	497	498	499
0	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	...	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000
1	1.002406	1.001230	0.998964	1.004901	0.998732	0.995485	1.005809	1.005240	0.995670	1.002249	...	1.002349	1.001143	1.004777	0.997456	0.998110	1.003788	0.995242	0.999131	0.998996	1.002754
2	1.002820	1.000942	1.001263	1.001527	1.008880	0.997732	1.014629	1.009174	0.990208	0.998309	...	0.997837	1.006001	1.004855	0.996405	0.991924	1.015263	0.993499	0.992840	0.998400	0.995199
3	1.009061	1.002575	1.000881	1.000417	1.012282	0.992918	1.017654	1.002783	0.994560	0.998209	...	1.003763	1.007444	1.006510	0.997607	0.991335	1.007901	0.987671	0.996186	1.001325	0.985407
4	1.016086	0.998357	1.000422	1.004517	1.013847	0.993105	1.013339	0.998971	0.993129	0.999413	...	1.000209	1.005411	1.008640	1.002152	0.996362	1.000718	0.986652	0.987922	1.014331	0.991284
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
7556	28.030911	37.405939	24.668193	47.209859	22.520441	23.652848	31.651462	30.615852	39.231077	23.520117	...	68.341627	27.349744	31.023659	35.284010	50.207835	27.869737	23.344379	16.620935	34.525911	62.741476
7557	28.100679	37.350037	24.743653	47.183273	22.516468	23.647467	31.650875	30.826689	39.390006	23.566772	...	68.091843	27.457265	30.901467	35.299930	50.077861	27.786322	23.383160	16.627983	34.585619	62.590230
7558	27.984769	37.398970	24.712191	47.169630	22.426692	23.778172	31.644927	30.773266	39.758511	23.676854	...	67.902286	27.521867	30.885856	34.943243	50.344072	27.869713	23.441258	16.519794	34.336454	62.439266
7559	27.884567	37.587090	24.718771	47.193397	22.449825	23.950313	31.626778	30.950165	39.652674	23.614836	...	68.242568	27.588266	30.868128	35.243832	50.149038	27.944404	23.613556	16.542526	34.318662	62.309319
7560	27.879213	37.317320	24.883653	47.342378	22.320469	23.949491	31.662563	31.219180	39.750457	23.624769	...	68.090630	27.645684	30.789391	35.544020	50.010503	28.034279	23.570843	16.679932	34.583051	62.885517

	AGG						SPY
	open	high	low	close	volume	daily_return	open	high	low	close	volume	daily_return
time
2021-06-08 00:00:00-04:00	114.84	114.87	114.7800	114.82	3951702	NaN	423.11	423.21	420.32	422.27	40131441	NaN
2021-06-09 00:00:00-04:00	115.43	115.43	114.9700	115.05	3418086	0.002003	423.18	423.26	421.41	421.62	38368598	-0.001539
2021-06-10 00:00:00-04:00	115.30	115.31	114.8605	115.30	3665934	0.002173	422.96	424.63	421.55	423.58	43577329	0.004649
2021-06-11 00:00:00-04:00	115.31	115.32	115.1600	115.24	3476373	-0.000520	424.19	424.42	422.82	424.31	37018077	0.001723
2021-06-14 00:00:00-04:00	115.18	115.18	114.9350	114.97	2999623	-0.002343	424.43	425.29	423.10	425.29	33128340	0.002310

etf_analyzer.ipynb

Evaluate the MFST Holdings by Using the Alpaca SDK¶

Review the total number of shares held in (MSFT).¶

Step 1: In the Starter_Code folder, create an environment file (.env) to store the values of your Alpaca API key and Alpaca secret key.¶

Step 2: Set the variables for the Alpaca API and secret keys. Using the Alpaca SDK, create the Alpaca tradeapi.REST object. In this object, include the parameters for the Alpaca API key, the secret key, and the version number.¶

Step 3: Set the following parameters for the Alpaca API call:¶

Step 4: Get the current closing prices for MSFT by using the Alpaca get_barset function. Format the response as a Pandas DataFrame by including the df property at the end of the get_barset function.¶

Step 5: Navigating the Alpaca response DataFrame, select the 'MSFT` closing prices, and store them as variables.¶

Step 2: Run a Monte Carlo simulation of 500 samples and 10 years for portfolio, and then plot the results.¶

Step 3: Plot the probability distribution of the Monte Carlo simulation.¶

Step 4: Generate the summary statistics for the Monte Carlo simulation.¶

Analyze the Retirement Portfolio Forecasts¶

The Power of Dividend Reinvestment with APIs, CSV and Montecarlo Simulations¶

Evaluate the Stock Holdings by Using the Alpaca SDK¶

1: Create an environment file (.env) to store the values of your Alpaca API key and Alpaca secret key.¶

2. Set the variables for the Alpaca API and secret keys. Using the Alpaca SDK, create the Alpaca tradeapi.REST object. In this object, include the parameters for the Alpaca API key, the secret key, and the version number.¶

3: Set the following parameters for the Alpaca API call:¶

4: Get the current closing prices for MSFT by using the Alpaca get_barset function. Format the response as a Pandas DataFrame by including the df property at the end of the get_barset function.¶

5: Navigating the Alpaca response DataFrame, select the MSFT closing prices, and store them as variables.¶

6: Calculate the value, in US dollars, of the current amount of shares of the stock portfolio, and print the results.¶

Use the read_csv function and the Path module to read the msft_dividends.csv file into a Pandas DataFrame. Review the first five rows of the DataFrame by using the head function.¶

Use the str.replace function to remove the dollar signs ($) from the values in the Amount column.¶

Convert the data type of the column to a float.¶

Choose columns of data on which to focus our analysis.¶

Calculating the average dividends for MSFT¶

Evaluate the Bitcoin Value by Using the Requests Library¶

Review the endpoint URLs for the API calls to Free Crypto API in order to get the current pricing information for BTC.¶

1. Use the Requests library to get the current price (in US dollars) of Bitcoin (BTC) by using the API endpoints.¶

2.Navigate the JSON response object to access the current price of Bitcoin, and store it in a variable.¶

Option 1: Reinvesting the dividens into Microsoft (MSFT)¶

Create the Monte Carlo Simulation that will include reinstment of dividend on a yearly basis¶

Step 1: Make an API call via the Alpaca SDK to get 5 years of historical closing prices for Microsoft.¶

Step 2a: Run a Monte Carlo simulation of 1000 samples and 10 years for Microsoft, and then plot the results.¶

Plot the probability distribution of the Monte Carlo simulation.¶

Generate the summary statistics for the Monte Carlo simulation.¶

Analyze the Dividend Reinvestment Into Microsoft Portfolio Forecasts¶

Step 2b: Run a Monte Carlo simulation of 1000 samples and 20 years for Microsoft, and then plot the results.¶

Plot the probability distribution of the Monte Carlo simulation.¶

Generate the summary statistics for the Monte Carlo simulation.¶

Analyze the Dividend Reinvestment Into Microsoft Portfolio Forecasts¶

Step 2c: Run a Monte Carlo simulation of 1000 samples and 30 years for Microsoft, and then plot the results.¶

Plot the probability distribution of the Monte Carlo simulation.¶

Generate the summary statistics for the Monte Carlo simulation.¶

Analyze the Dividend Reinvestment Into Microsoft Portfolio Forecasts¶

Option 2: Reinvesting the dividens into Bitcoin Cryptocurrency (BTC)¶

Create the Monte Carlo Simulation that will include reinstment of dividend on a yearly basis¶

Option 3: Keep the dividens as cash and see where Microsoft (MSFT) could be without reinvesting¶

Create the Monte Carlo Simulation that will include reinstment of dividend on a yearly basis¶

Create a Web Application for an ETF Analyzer¶

Analyze a Single Asset in the ETF¶

Optimize Data Access with Advanced SQL Queries¶

Analyze the ETF Portfolio¶

Deploy the Notebook as a Web Application¶

Review the following code which imports the required libraries, initiates your SQLite database, popluates the database with records from the etf.db seed file that was included in your Starter_Code folder, creates the database engine, and confirms that data tables that it now contains.¶

Analyze a single asset in the FinTech ETF¶

Step 1: Write a SQL SELECT statement by using an f-string that reads all the PYPL data from the database. Using the SQL SELECT statement, execute a query that reads the PYPL data from the database into a Pandas DataFrame.¶

Step 2: Use the head and tail functions to review the first five and the last five rows of the DataFrame. Make a note of the beginning and end dates that are available from this dataset. You’ll use this information to complete your analysis.¶

Step 3: Using hvPlot, create an interactive visualization for the PYPL daily returns. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.¶

Step 4: Using hvPlot, create an interactive visualization for the PYPL cumulative returns. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.¶

Optimize the SQL Queries¶

Step 1: Access the closing prices for PYPL that are greater than 200 by completing the following steps:¶

Step 2: Find the top 10 daily returns for PYPL by completing the following steps:¶

Analyze the Fintech ETF Portfolio¶

Step 1: Write a SQL query to join each table in the portfolio into a single DataFrame. To do so, complete the following steps:¶

Step 2: Create a DataFrame that averages the “daily_returns” columns for all four assets. Review the resulting DataFrame.¶

Step 3: Use the average daily returns in the etf_portfolio_returns DataFrame to calculate the annualized returns for the portfolio. Display the annualized return value of the ETF portfolio.¶

Step 4: Use the average daily returns in the etf_portfolio_returns DataFrame to calculate the cumulative returns of the ETF portfolio.¶

Step 5: Using hvPlot, create an interactive line plot that visualizes the cumulative return values of the ETF portfolio. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.¶

Import the data¶

Calculate and Plot the Housing Units per Year¶

Step 1: Use the groupby function to group the data by year. Aggregate the results by the mean of the groups.¶

Step 2: Use the hvplot function to plot the housing_units_by_year DataFrame as a bar chart. Make the x-axis represent the year and the y-axis represent the housing_units.¶

Step 3: Style and format the line plot to ensure a professionally styled visualization.¶

Step 5: Answer the following question:¶

Calculate and Plot the Average Sale Prices per Square Foot¶

Step 1: Group the data by year, and then average the results.¶

Step 2: Create a new DataFrame named prices_square_foot_by_year by filtering out the “housing_units” column. The new DataFrame should include the averages per year for only the sale price per square foot and the gross rent.¶

Step 3: Use hvPlot to plot the prices_square_foot_by_year DataFrame as a line plot.¶

Step 4: Style and format the line plot to ensure a professionally styled visualization.¶

Step 6: Use both the prices_square_foot_by_year DataFrame and interactive plots to answer the following questions:¶

Step 1: In the `Starter_Code` folder, create an environment file (`.env`) to store the values of your Alpaca API key and Alpaca secret key.¶

Step 2: Set the variables for the Alpaca API and secret keys. Using the Alpaca SDK, create the Alpaca `tradeapi.REST` object. In this object, include the parameters for the Alpaca API key, the secret key, and the version number.¶

Step 4: Get the current closing prices for `MSFT` by using the Alpaca `get_barset` function. Format the response as a Pandas DataFrame by including the `df` property at the end of the `get_barset` function.¶

1: Create an environment file (`.env`) to store the values of your Alpaca API key and Alpaca secret key.¶

2. Set the variables for the Alpaca API and secret keys. Using the Alpaca SDK, create the Alpaca `tradeapi.REST` object. In this object, include the parameters for the Alpaca API key, the secret key, and the version number.¶

4: Get the current closing prices for `MSFT` by using the Alpaca `get_barset` function. Format the response as a Pandas DataFrame by including the `df` property at the end of the `get_barset` function.¶

5: Navigating the Alpaca response DataFrame, select the `MSFT` closing prices, and store them as variables.¶

Use the `read_csv` function and the `Path` module to read the `msft_dividends.csv` file into a Pandas DataFrame. Review the first five rows of the DataFrame by using the `head` function.¶

Use the `str.replace` function to remove the dollar signs ($) from the values in the Amount column.¶

Convert the data type of the column to a `float`.¶

Review the following code which imports the required libraries, initiates your SQLite database, popluates the database with records from the `etf.db` seed file that was included in your Starter_Code folder, creates the database engine, and confirms that data tables that it now contains.¶

Step 1: Write a SQL `SELECT` statement by using an f-string that reads all the PYPL data from the database. Using the SQL `SELECT` statement, execute a query that reads the PYPL data from the database into a Pandas DataFrame.¶

Step 2: Use the `head` and `tail` functions to review the first five and the last five rows of the DataFrame. Make a note of the beginning and end dates that are available from this dataset. You’ll use this information to complete your analysis.¶

Step 4: Use the average daily returns in the `etf_portfolio_returns` DataFrame to calculate the cumulative returns of the ETF portfolio.¶

Step 1: Use the `groupby` function to group the data by year. Aggregate the results by the `mean` of the groups.¶

Step 2: Use the `hvplot` function to plot the `housing_units_by_year` DataFrame as a bar chart. Make the x-axis represent the `year` and the y-axis represent the `housing_units`.¶

Step 2: Create a new DataFrame named `prices_square_foot_by_year` by filtering out the “housing_units” column. The new DataFrame should include the averages per year for only the sale price per square foot and the gross rent.¶

Step 3: Use hvPlot to plot the `prices_square_foot_by_year` DataFrame as a line plot.¶

Step 6: Use both the `prices_square_foot_by_year` DataFrame and interactive plots to answer the following questions:¶

Step 1: Create a new DataFrame that groups the original DataFrame by year and neighborhood. Aggregate the results by the `mean` of the groups.¶

Step 2: Filter out the “housing_units” column to create a DataFrame that includes only the `sale_price_sqr_foot` and `gross_rent` averages per year.¶

Step 3: Create an interactive line plot with hvPlot that visualizes both `sale_price_sqr_foot` and `gross_rent`. Set the x-axis parameter to the year (`x="year"`). Use the `groupby` parameter to create an interactive widget for `neighborhood`.¶

Step 1: Read the `neighborhood_coordinates.csv` file from the `Resources` folder into the notebook, and create a DataFrame named `neighborhood_locations_df`. Be sure to set the `index_col` of the DataFrame as “Neighborhood”.¶

Step 2: Using the original `sfo_data_df` Dataframe, create a DataFrame named `all_neighborhood_info_df` that groups the data by neighborhood. Aggregate the results by the `mean` of the group.¶

Step 3: Review the two code cells that concatenate the `neighborhood_locations_df` DataFrame with the `all_neighborhood_info_df` DataFrame.¶

Step 4: Using hvPlot with GeoViews enabled, create a `points` plot for the `all_neighborhoods_df` DataFrame. Be sure to do the following:¶

Step 1: Create a variable named `monthly_income`, and set its value to `12000`.¶

Step 1: In the `Starter_Code` folder, create an environment file (`.env`) to store the values of your Alpaca API key and Alpaca secret key.¶

Step 2: Set the variables for the Alpaca API and secret keys. Using the Alpaca SDK, create the Alpaca `tradeapi.REST` object. In this object, include the parameters for the Alpaca API key, the secret key, and the version number.¶

Step 4: Get the current closing prices for `SPY` and `AGG` by using the Alpaca `get_barset` function. Format the response as a Pandas DataFrame by including the `df` property at the end of the `get_barset` function.¶

Step 5: Navigating the Alpaca response DataFrame, select the `SPY` and `AGG` closing prices, and store them as variables.¶

Step 1: Create a Python list named `savings_data` that has two elements. The first element contains the total value of the cryptocurrency wallet. The second element contains the total value of the stock and bond portions of the portfolio.¶

Step 2: Use the `savings_data` list to create a Pandas DataFrame named `savings_df`, and then display this DataFrame. The function to create the DataFrame should take the following three parameters:¶

Step 3: Use the `savings_df` DataFrame to plot a pie chart that visualizes the composition of the member’s portfolio. The y-axis of the pie chart uses `amount`. Be sure to add a title.¶

Step 4-1: Create a variable named `emergency_fund_value`, and set it equal to three times the value of the member’s `monthly_income` of 12000. (You set this earlier in Part 1).¶